package school.repository;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import school.domain.Group;

import com.ibm.db2.jcc.a.SqlException;

public class JdbcGroupDao implements GroupDao {

	@Override
	public Group getByGroupId(int groupId) throws Exception {
		Connection con = JdbcUtil.connect();
		Group group = new Group();

		String sql = "CALL Get_Group(?,?,?,?,?,?,?,?,?,?)";
		
		CallableStatement prepCall = con.prepareCall(sql);
		
		prepCall.setInt(1, groupId);
		prepCall.registerOutParameter(2, Types.INTEGER);
		prepCall.registerOutParameter(3, Types.VARCHAR);
		prepCall.registerOutParameter(4, Types.INTEGER);
		prepCall.registerOutParameter(5, Types.INTEGER);
		prepCall.registerOutParameter(6, Types.VARCHAR);
		prepCall.registerOutParameter(7, Types.INTEGER);
		prepCall.registerOutParameter(8, Types.INTEGER);
		prepCall.registerOutParameter(9, Types.INTEGER);
		prepCall.registerOutParameter(10, Types.VARCHAR);

		try {
			prepCall.execute();
			
			group.setId(groupId);
			group.setActive(prepCall.getInt(2) != 0);
			group.setName(prepCall.getString(3));
			group.setLimit(prepCall.getInt(4));
			group.setStaffId(prepCall.getInt(5));
			group.setCategoryName(prepCall.getString(6));
			group.setRequiredAge(prepCall.getInt(7));
			group.setRequiredTheory(prepCall.getInt(8));
			group.setRequiredPractice(prepCall.getInt(9));
			group.setStaffName(prepCall.getString(10));
			
		} catch (Exception e) {
			throw e;
		} finally {
			JdbcUtil.disconnect();
		}
		
		return group;
	}
	
	@Override
	public ArrayList<Group> getAvailable(int customerId) throws Exception {
		Connection con = JdbcUtil.connect();
		
		String sql = "CALL Get_Available_Groups(?)";
		ArrayList<Group> groups = new ArrayList<Group>();
		
		CallableStatement prepCall = con.prepareCall(sql);
		prepCall.setInt(1, customerId);
		
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
		
		try {
			ResultSet rs = prepCall.executeQuery();
			System.out.println("JdbcGroupDao "+rs.getFetchSize());
			Date dateStarted;
			java.util.Date today = new java.util.Date();
			
			while(rs.next()) {
				boolean started = false;
				Group group = new Group();
				
				group.setId(rs.getInt(1));
				group.setActive(rs.getInt(2) != 0);
				group.setName(rs.getString(3));
				group.setLimit(rs.getInt(4));
				group.setStaffId(rs.getInt(5));
				group.setCategoryName(rs.getString(6));
				group.setRequiredAge(rs.getInt(7));
				group.setRequiredTheory(rs.getInt(8));
				group.setRequiredPractice(rs.getInt(9));
				group.setStaffName(rs.getString(10));
				
				dateStarted = rs.getDate(11);
				
				if(dateStarted != null) {
					group.setStartDate(dateFormat.format(dateStarted));
					if(dateStarted.compareTo(today) < 0) {
						started = true;
					}
				}
				else group.setStartDate("wkrótce");
				
				if(!started) {
					groups.add(group);
				}
			}
		} catch (SqlException e) {
			e.printStackTrace();
			throw e;
		} catch (Exception e) {
			throw e;
		} finally {
			JdbcUtil.disconnect();
		}
		
		return groups;
	}

}
